﻿Imports Business
Imports DTO
Imports System.Globalization
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop

Public Class frmEmployeeAttendance

    Dim empName As String

    Dim empNo As String

    ''' <summary>
    ''' employee ex to
    ''' </summary>
    ''' <remarks></remarks>
    Dim exFrom As Integer

    ''' <summary>
    ''' employee ex to
    ''' </summary>
    ''' <remarks></remarks>
    Dim exTo As Integer
    'Dim day As Date

    Dim organNo As String

    Dim organRootNo As String

    Dim startDate As Date

    Dim endDate As Date

    Dim month As Integer

    Dim year As Integer

    Dim bsTimetable As BSTimetable

    ' se thay doi khi click button next previous
    Dim dtTimetable As Data.DataTable

    Dim bsWorkData As BSWorkData

    ' list of row has edited
    Dim arrRowEdited As ArrayList

    ' flag if datagridview has edited
    Dim grdIsEdited As Boolean = False

    'Data.DataTable type of duty day
    Dim dtDutyType As Data.DataTable = New Data.DataTable()

    Dim colDate As DataGridViewColumn
    Dim colOnDuty As DataGridViewColumn
    Dim colOffDuty As DataGridViewColumn
    Dim cmbdtDutyType As DataGridViewComboBoxColumn
    Dim cmbWorkType As DataGridViewComboBoxColumn
    Dim colOnDutyA As DataGridViewColumn
    Dim colOffDutyA As DataGridViewColumn
    Dim colRecess As DataGridViewColumn
    Dim colWork As DataGridViewColumn
    Dim colOvertime As DataGridViewColumn
    ' Dim colMidnight As DataGridViewColumn
    Dim colHoliday As DataGridViewColumn
    'Dim colHolidayMidnight As DataGridViewColumn
    'Dim colPaidTimeOff As DataGridViewColumn
    Dim colLateness As DataGridViewColumn
    Dim colLeaveEarly As DataGridViewColumn
    Dim colMemo As DataGridViewColumn

    Dim strColDate As String = "Date"
    Dim strColOnDuty As String = "On duty"
    Dim strColOffDuty As String = "Off duty"
    Dim strColDutyType As String = "Duty type"
    Dim strColWorkType As String = "Timetable"
    Dim strColOnDutyA As String = "On duty amendment"
    Dim strColOffDutyA As String = "Off duty amendment"
    Dim strColRecess As String = "Recess"
    Dim strColWork As String = "Work"
    Dim strColOT As String = "Overtime"
    'Dim strColMidnight As String = "Midnight"
    Dim strColHoliday As String = "Holiday"
    'Dim strColHolidayMidnight As String = "Holiday's midnight"
    Dim strColLateness As String = "Lateness"
    Dim strColLeaveEarly As String = "Early leave"
    Dim strColMemo As String = "Memo"

    Dim strDTColNo As String = "NO"
    Dim strDTColName As String = "NAME"
    Dim dutyType1 As String = "Normal Duty"
    Dim dutyType2 As String = "Paid Leave"
    Dim dutyType3 As String = "Half Day Leave(AM)"
    Dim dutyType4 As String = "Half Day Leave(PM)"
    Dim dutyType5 As String = "Special Leave"
    Dim dutyType6 As String = "Replacement Leave"
    Dim dutyType7 As String = "Absence"
    Dim dutyType8 As String = "Holiday Duty"
    Dim dutyType9 As String = "Substitute Duty"
    Dim dutyType10 As String = "Sick Leave"
    Dim dutyType11 As String = "Half Day Special Leave"
    Dim dutyType12 As String = "Half Day Sick Leave"
    Dim dutyType13 As String = "Unpaid Half Day Leave"

    'Dim dtWorkDate As Data.DataTable
    ''' <summary>
    ''' contract minutes
    ''' </summary>
    ''' <remarks></remarks>
    Dim contract As Integer
    ''' <summary>
    ''' working minutes
    ''' </summary>
    ''' <remarks></remarks>
    Dim working As Integer
    ''' <summary>
    ''' overtime minutes
    ''' </summary>
    ''' <remarks></remarks>
    Dim overtime As Integer
    ''' <summary>
    ''' holiday minutes
    ''' </summary>
    ''' <remarks></remarks>
    Dim holiday As Integer
    ''' <summary>
    ''' paid leave
    ''' </summary>
    ''' <remarks></remarks>
    Dim paidLeave As Double
    Dim replace As Double
    Dim absence As Double
    Dim special As Double
    Dim lateness As Integer
    Dim leaveEarly As Integer

    Dim xlApp As Excel.Application

    Dim xlBook As Excel.Workbook

    Dim xlSheet As Excel.Worksheet

    ' Dim flagUserEdit As Boolean
    Dim serverName As String


    Public Sub New(ByVal empNo As String, ByVal empName As String, ByVal organNo As String, ByVal organRootNo As String,
                   ByVal startDate As DateTime, ByVal endDate As DateTime, ByVal exFrom As String,
                   ByVal exTo As String, ByVal month As Integer, ByVal year As Integer, ByVal serverName As String)


        ' This call is required by the designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        Me.serverName = serverName

        Me.empName = empName
        Me.empNo = empNo
        'Me.day = day
        Me.organNo = organNo

        Me.organRootNo = organRootNo

        Me.startDate = startDate
        Me.endDate = endDate

        Me.exFrom = Integer.Parse(exFrom)

        Me.exTo = Integer.Parse(exTo)

        Me.month = month

        Me.year = year

        txtEmpName.Text = Me.empName

        txtPeriodOfTime.Text = Me.startDate.ToString("dd/MMMM/yyyy") & " - " & Me.endDate.ToString("dd/MMMM/yyyy")

        bsTimetable = New BSTimetable(serverName)

        dtTimetable = New Data.DataTable()

        'get timetable effect 
        dtTimetable = bsTimetable.GetTimetableEffect(startDate.ToString("yyyyMMdd"), endDate.ToString("yyyyMMdd"))

        'bs workdata
        bsWorkData = New BSWorkData(Me.serverName)

        ' array list contain index rows edited
        arrRowEdited = New ArrayList()


        ' add Duty type
        dtDutyType.Columns.Add(strDTColNo)
        dtDutyType.Columns.Add(strDTColName)
        dtDutyType.Rows.Add("1", dutyType1)
        dtDutyType.Rows.Add("2", dutyType2)
        dtDutyType.Rows.Add("10", dutyType10)
        dtDutyType.Rows.Add("5", dutyType5)
        dtDutyType.Rows.Add("7", dutyType7)
        dtDutyType.Rows.Add("3", dutyType3)
        dtDutyType.Rows.Add("4", dutyType4)
        dtDutyType.Rows.Add("12", dutyType12)
        dtDutyType.Rows.Add("11", dutyType11)
        dtDutyType.Rows.Add("13", dutyType13)
        dtDutyType.Rows.Add("6", dutyType6)
        dtDutyType.Rows.Add("8", dutyType8)
        dtDutyType.Rows.Add("9", dutyType9)


        ' insert colums datagridview work data
        ' Template column
        Dim cell As DataGridViewCell = New DataGridViewTextBoxCell()
        ' colum date
        colDate = New DataGridViewColumn()
        colDate.HeaderText = strColDate
        colDate.Name = strColDate
        colDate.ReadOnly = True
        colDate.CellTemplate = cell
        colDate.Frozen = True
        grdWorkData.Columns.Add(colDate)

        ' column on duty
        colOnDuty = New DataGridViewColumn()
        colOnDuty.HeaderText = strColOnDuty
        colOnDuty.Name = strColOnDuty
        colOnDuty.CellTemplate = cell
        colOnDuty.Frozen = True
        grdWorkData.Columns.Add(colOnDuty)
        ' readonly
        colOnDuty.ReadOnly = True
        colOnDuty.DefaultCellStyle.BackColor = Color.LightGray

        ' column off duty
        colOffDuty = New DataGridViewColumn()
        colOffDuty.HeaderText = strColOffDuty
        colOffDuty.Name = strColOffDuty
        colOffDuty.CellTemplate = cell
        colOffDuty.Frozen = True
        grdWorkData.Columns.Add(colOffDuty)
        ' readonly
        colOffDuty.ReadOnly = True
        colOffDuty.DefaultCellStyle.BackColor = Color.LightGray

        ' column Duty type
        cmbdtDutyType = New DataGridViewComboBoxColumn()
        cmbdtDutyType.HeaderText = strColDutyType
        cmbdtDutyType.Name = strColDutyType
        'cmbdtDutyType.Name = "cmbDType"
        cmbdtDutyType.DisplayStyle = ComboBoxStyle.DropDownList
        cmbdtDutyType.DataSource = dtDutyType
        cmbdtDutyType.DisplayMember = strDTColName
        cmbdtDutyType.ValueMember = strDTColNo
        cmbdtDutyType.Frozen = True
        grdWorkData.Columns.Add(cmbdtDutyType)

        ' column time table
        cmbWorkType = New DataGridViewComboBoxColumn()
        cmbWorkType.HeaderText = strColWorkType
        cmbWorkType.Name = strColWorkType
        'cmbWorkType.Name = "cmbTimetable"
        cmbWorkType.DataSource = dtTimetable
        cmbWorkType.DisplayMember = "TIME_TABLE_NAME"
        cmbWorkType.ValueMember = "NO"
        cmbWorkType.DisplayStyle = ComboBoxStyle.DropDownList
        cmbWorkType.Frozen = True
        grdWorkData.Columns.Add(cmbWorkType)

        ' column on duty A
        colOnDutyA = New DataGridViewColumn()
        colOnDutyA.HeaderText = strColOnDutyA
        colOnDutyA.Name = strColOnDutyA
        colOnDutyA.CellTemplate = cell
        grdWorkData.Columns.Add(colOnDutyA)

        ' colum off duty A
        colOffDutyA = New DataGridViewColumn()
        colOffDutyA.HeaderText = strColOffDutyA
        colOffDutyA.Name = strColOffDutyA
        colOffDutyA.CellTemplate = cell
        grdWorkData.Columns.Add(colOffDutyA)

        ' column work
        colWork = New DataGridViewColumn()
        colWork.HeaderText = strColWork
        colWork.Name = strColWork
        colWork.CellTemplate = cell
        grdWorkData.Columns.Add(colWork)

        ' column recess
        colRecess = New DataGridViewColumn()
        colRecess.HeaderText = strColRecess
        colRecess.Name = strColRecess
        colRecess.CellTemplate = cell
        grdWorkData.Columns.Add(colRecess)

        'column overtime
        colOvertime = New DataGridViewColumn()
        colOvertime.HeaderText = strColOT
        colOvertime.Name = strColOT
        colOvertime.CellTemplate = cell
        grdWorkData.Columns.Add(colOvertime)

        '' column midnight
        'colMidnight = New DataGridViewColumn()
        'colMidnight.HeaderText = strColMidnight
        'colMidnight.Name = strColMidnight
        'colMidnight.CellTemplate = cell
        'grdWorkData.Columns.Add(colMidnight)

        ' column holiday
        colHoliday = New DataGridViewColumn()
        colHoliday.HeaderText = strColHoliday
        colHoliday.Name = strColHoliday
        colHoliday.CellTemplate = cell
        grdWorkData.Columns.Add(colHoliday)

        '' column holiday midnight
        'colHolidayMidnight = New DataGridViewColumn()
        'colHolidayMidnight.HeaderText = strColHolidayMidnight
        'colHolidayMidnight.Name = strColHolidayMidnight
        'colHolidayMidnight.CellTemplate = cell
        'grdWorkData.Columns.Add(colHolidayMidnight)

        '' column paid time off
        'colPaidTimeOff = New DataGridViewColumn()
        'colPaidTimeOff.HeaderText = "Thời gian có lương"
        'colPaidTimeOff.CellTemplate = cell
        'dgvWorkData.Columns.Add(colPaidTimeOff)

        ' column lateness
        colLateness = New DataGridViewColumn()
        colLateness.HeaderText = strColLateness
        colLateness.Name = strColLateness
        colLateness.CellTemplate = cell
        grdWorkData.Columns.Add(colLateness)

        ' column early leave
        colLeaveEarly = New DataGridViewColumn()
        colLeaveEarly.HeaderText = strColLeaveEarly
        colLeaveEarly.Name = strColLeaveEarly
        colLeaveEarly.CellTemplate = cell
        grdWorkData.Columns.Add(colLeaveEarly)

        ' column memo
        colMemo = New DataGridViewColumn()
        colMemo.HeaderText = strColMemo
        colMemo.Name = strColMemo
        colMemo.CellTemplate = cell
        colMemo.Width = 500
        grdWorkData.Columns.Add(colMemo)




    End Sub


    ''' <summary>
    ''' Load data grid view work data
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub LoadWorkData()

        ' start date and end date in one month
        If startDate.Month = endDate.Month Then

            AddRowWorkData(startDate, endDate)


        Else
            ' start date and end date in two months
            ' Get number of days in the month containing start date
            Dim numOfDate As Integer = Date.DaysInMonth(startDate.Year, startDate.Month)

            ' get last date in this month
            Dim lastDate As Date = New Date(startDate.Year, startDate.Month, numOfDate)

            ' add days in the first half
            AddRowWorkData(startDate, lastDate)

            ' get the first date in the second month
            Dim firstDate As Date = New Date(endDate.Year, endDate.Month, 1)

            ' add days in the sencond month
            AddRowWorkData(firstDate, endDate)

        End If

        '' calculate and update
        'Dim arrayListUpdate As ArrayList = New ArrayList()

        'For i As Integer = 0 To grdWorkData.Rows.Count-1

        '    ' adjust acording to FP terminal data
        '    flagUserEdit = False

        '    AdjustTimeDutyType(i)

        '    If Not grdWorkData.Rows.Item(i).Cells.Item(strColOnDuty) Is Nothing Or
        '        Not grdWorkData.Rows.Item(i).Cells.Item(strColOffDuty) Is Nothing Then

        '        arrayListUpdate.Add(i)

        '    End If

        'Next

        '' update to database 
        'UpdateWorkDate(arrayListUpdate)

        'calculate details
        CalculateWorkDataDetails()

        'load details
        LoadDetails()

    End Sub


    ''' <summary>
    ''' Load details
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub LoadDetails()

        txtWorking.Text = Business.Functions.ConvertMinutesToHours(working)

        txtContractHr.Text = Business.Functions.ConvertMinutesToHours(contract)

        txtOvertime.Text = Business.Functions.ConvertMinutesToHours(overtime)

        txtHoliday.Text = Business.Functions.ConvertMinutesToHours(holiday)

        txtPaidLeave.Text = paidLeave.ToString()

        txtReplace.Text = replace.ToString()

        txtAbsence.Text = absence.ToString()

        txtSpecial.Text = special.ToString()

        txtLateness.Text = lateness.ToString()

        txtEarly.Text = leaveEarly.ToString()

    End Sub

    ''' <summary>
    ''' Add row into datagridview work data 
    ''' startdate.Month = endDate.Month, and calculate details
    ''' </summary>
    ''' <param name="startDate"></param>
    ''' <param name="endDate"></param>
    ''' <remarks></remarks>
    Private Sub AddRowWorkData(ByVal startDate As Date, ByVal endDate As Date)

        Dim day As String

        ' check employee exfrom, ex to, if exist 2 employee in one month
        If exFrom > Integer.Parse(startDate.ToString("yyyyMMdd")) And exTo > Integer.Parse(endDate.ToString("yyyyMMdd")) Then

            startDate = DateTime.ParseExact(Functions.ConvertstrDBDateToStrDate(exFrom), "d", New CultureInfo("fr-FR"))

            endDate = endDate

        ElseIf exFrom < Integer.Parse(startDate.ToString("yyyyMMdd")) And exTo < Integer.Parse(endDate.ToString("yyyyMMdd")) Then

            startDate = startDate

            endDate = DateTime.ParseExact(Functions.ConvertstrDBDateToStrDate(exTo), "d", New CultureInfo("fr-FR"))

        Else

            startDate = startDate

            endDate = endDate

        End If


        'get work data '''''' trong ni co phai trong 1 thang dau minh chia ra ma coi lai
        Dim dtWorkData As Data.DataTable = bsWorkData.GetWorkData(organNo, empNo, startDate.ToString("yyyyMMdd"), endDate.ToString("yyyyMMdd"))

        '' calculate all detail work data employee
        'bsWorkData.CalculateDetailWorkData(dtWorkData, contract, working, overtime, holiday, paidLeave, replace, absence, special,
        '                                   lateness, leaveEarly)


        For i As Integer = startDate.Day To endDate.Day

            Dim row As DataGridViewRow = New DataGridViewRow()

            ' convert to format date in DB
            day = startDate.ToString("yyyy") & startDate.ToString("MM") & i.ToString("00")

            ' create temporary date to set cell value
            Dim d As Date = New Date(startDate.Year, startDate.Month, i)

            Dim cell As DataGridViewCell = New DataGridViewTextBoxCell()

            ' Date cell
            cell.Value = d.ToString("dd/MM/yyyy(ddd)")

            row.Cells.Add(cell)

            Dim bsHoliday As BSHoliday = New BSHoliday(Me.serverName)

            ' check holiday
            If bsHoliday.CheckHoliday(Me.organNo, d.ToString("yyyyMMdd")) Then

                row.DefaultCellStyle.BackColor = Color.LightPink

            End If

            Dim isAdded As Boolean = False

            ' get data in work data
            For Each r As DataRow In dtWorkData.Rows

                If r.Item("WORK_DATE").ToString().Equals(day) Then

                    ' on duty cell
                    cell = New DataGridViewTextBoxCell()

                    If Not IsDBNull(r.Item("START_TIME")) Then

                        cell.Value = Double.Parse(r.Item("START_TIME")).ToString("0#:##")

                    Else

                        cell.Value = ""

                    End If

                    row.Cells.Add(cell)


                    ' off duty cell
                    cell = New DataGridViewTextBoxCell()

                    If Not IsDBNull(r.Item("END_TIME")) Then

                        cell.Value = Integer.Parse(r.Item("END_TIME")).ToString("0#:##")

                    Else

                        cell.Value = ""

                    End If

                    row.Cells.Add(cell)


                    ' duty type cell
                    Dim cbCell As DataGridViewComboBoxCell = New DataGridViewComboBoxCell()

                    cbCell.DataSource = dtDutyType

                    cbCell.DisplayMember = "Name"

                    cbCell.ValueMember = "No"

                    cbCell.DisplayStyle = ComboBoxStyle.DropDownList

                    Dim index As Integer = 0

                    For j As Integer = 0 To dtDutyType.Rows.Count - 1

                        If dtDutyType.Rows.Item(j).Item("No").ToString().Equals(r.Item("WORK_TYPE_NO").ToString()) Then

                            index = j

                        End If

                    Next

                    cbCell.Value = dtDutyType.Rows(index)(0)

                    row.Cells.Add(cbCell)


                    ' get dt time table
                    Dim dt As Data.DataTable = bsTimetable.GetTimetableEffect(day, day)


                    ' time table cell
                    cbCell = New DataGridViewComboBoxCell()

                    cbCell.DataSource = dt

                    cbCell.DisplayMember = "TIME_TABLE_NAME"

                    cbCell.ValueMember = "NO"

                    cbCell.DisplayStyle = ComboBoxStyle.DropDownList

                    index = 0

                    For j As Integer = 0 To dt.Rows.Count - 1

                        If dt.Rows.Item(j).Item("NO").ToString().Equals(r.Item("NO").ToString()) Then

                            index = j

                        End If

                    Next

                    cbCell.Value = dt.Rows(index)("NO")

                    row.Cells.Add(cbCell)


                    ' On duty A cell
                    cell = New DataGridViewTextBoxCell()

                    If Not IsDBNull(r.Item("UPDATE_START_TIME")) Then

                        cell.Value = Integer.Parse(r.Item("UPDATE_START_TIME")).ToString("00:##")

                    Else

                        cell.Value = ""

                    End If

                    row.Cells.Add(cell)


                    ' Off duty A cell
                    cell = New DataGridViewTextBoxCell()

                    If Not IsDBNull(r.Item("UPDATE_END_TIME")) Then

                        cell.Value = Integer.Parse(r.Item("UPDATE_END_TIME")).ToString("0#:##")

                    Else

                        cell.Value = ""

                    End If

                    row.Cells.Add(cell)

                    ' Work cell
                    cell = New DataGridViewTextBoxCell()

                    If Not IsDBNull(r.Item("WORKING_TIME")) Then

                        cell.Value = ConvertMinutesToHours(Integer.Parse(r.Item("WORKING_TIME")))

                    Else

                        cell.Value = ""

                    End If

                    row.Cells.Add(cell)


                    ' Recess cell
                    cell = New DataGridViewTextBoxCell()

                    If Not IsDBNull(r.Item("REST_TIME")) Then

                        cell.Value = ConvertMinutesToHours(Integer.Parse(r.Item("REST_TIME")))

                    Else

                        cell.Value = ""

                    End If

                    row.Cells.Add(cell)


                    ' overtime cell
                    cell = New DataGridViewTextBoxCell()

                    If Not IsDBNull(r.Item("OVER_TIME")) Then

                        cell.Value = ConvertMinutesToHours(Integer.Parse(r.Item("OVER_TIME")))

                    Else

                        cell.Value = ""

                    End If

                    row.Cells.Add(cell)


                    ''Midnight cell
                    'cell = New DataGridViewTextBoxCell()

                    'If Not IsDBNull(r.Item("LATE_NIGHT_TIME")) Then

                    '    cell.Value = ConvertMinutesToHours(Integer.Parse(r.Item("LATE_NIGHT_TIME")))

                    'Else

                    '    cell.Value = ""

                    'End If

                    'row.Cells.Add(cell)


                    ' Holiday cell
                    cell = New DataGridViewTextBoxCell()

                    If Not IsDBNull(r.Item("HOLIDAY_TIME")) Then

                        cell.Value = ConvertMinutesToHours(Integer.Parse(r.Item("HOLIDAY_TIME")))

                    Else

                        cell.Value = ""

                    End If

                    row.Cells.Add(cell)


                    '' Holiday's midnight cell
                    'cell = New DataGridViewTextBoxCell()

                    'If Not IsDBNull(r.Item("HOLIDAY_LATE_NIGHT_TIME")) Then

                    '    cell.Value = ConvertMinutesToHours(Integer.Parse(r.Item("HOLIDAY_LATE_NIGHT_TIME")))

                    'Else

                    '    cell.Value = ""

                    'End If

                    'row.Cells.Add(cell)


                    ' Lateness cell
                    cell = New DataGridViewTextBoxCell()

                    If Not IsDBNull(r.Item("BEING_LATE_TIME")) Then

                        cell.Value = ConvertMinutesToHours(Integer.Parse(r.Item("BEING_LATE_TIME")))

                    Else

                        cell.Value = ""

                    End If

                    row.Cells.Add(cell)

                    ' Early leave cell
                    cell = New DataGridViewTextBoxCell()

                    If Not IsDBNull(r.Item("LEAVING_EARLY_TIME")) Then

                        cell.Value = ConvertMinutesToHours(Integer.Parse(r.Item("LEAVING_EARLY_TIME")))

                    Else

                        cell.Value = ""

                    End If

                    row.Cells.Add(cell)

                    ' Memo cell
                    cell = New DataGridViewTextBoxCell()

                    If Not IsDBNull(r.Item("MEMO")) Then

                        cell.Value = (r.Item("MEMO")).ToString()

                    Else

                        cell.Value = ""

                    End If

                    row.Cells.Add(cell)

                    isAdded = True

                    Exit For

                End If

            Next

            ' check if it is not added
            If Not isAdded Then

                ' on duty cell
                cell = New DataGridViewTextBoxCell()

                cell.Value = ""

                row.Cells.Add(cell)


                ' off duty cell
                cell = New DataGridViewTextBoxCell()

                cell.Value = ""

                row.Cells.Add(cell)


                ' duty type cell
                Dim cbCell As DataGridViewComboBoxCell = New DataGridViewComboBoxCell()

                cbCell.DataSource = dtDutyType

                cbCell.DisplayMember = "Name"

                cbCell.ValueMember = "No"

                cbCell.DisplayStyle = ComboBoxStyle.DropDownList

                cbCell.Value = dtDutyType.Rows(0)(0)

                row.Cells.Add(cbCell)

                ' get dt time table
                Dim dt As Data.DataTable = bsTimetable.GetTimetableEffect(day, day)

                ' time table cell
                cbCell = New DataGridViewComboBoxCell()

                cbCell.DataSource = dt

                cbCell.DisplayMember = "TIME_TABLE_NAME"

                cbCell.ValueMember = "NO"

                cbCell.DisplayStyle = ComboBoxStyle.DropDownList

                cbCell.Value = dt.Rows(0)("NO")

                row.Cells.Add(cbCell)

            End If

            ' add to datagridview
            grdWorkData.Rows.Add(row)

        Next

    End Sub


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub frmEmployeeAttendance_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'load work data
        LoadWorkData()

    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click

        frmMain.RemoveTabpage()

    End Sub


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub grdWorkData_EditingControlShowing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles grdWorkData.EditingControlShowing

        Dim control As Control = e.Control

        If grdWorkData.CurrentCell.ColumnIndex <> 0 And grdWorkData.CurrentCell.ColumnIndex <> 3 And
            grdWorkData.CurrentCell.ColumnIndex <> 4 And grdWorkData.CurrentCell.ColumnIndex <> 13 Then

            RemoveHandler control.KeyPress, AddressOf Handle_KeyPress

            AddHandler control.KeyPress, AddressOf Handle_KeyPress

        Else

            RemoveHandler control.KeyPress, AddressOf Handle_KeyPress

        End If

    End Sub


    ''' <summary>
    ''' validate cell textbox 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub Handle_KeyPress(ByVal sender As Object, ByVal e As KeyPressEventArgs)

        Dim txtCell As System.Windows.Forms.TextBox = sender

        txtCell.MaxLength = 4

        If (Char.IsDigit(e.KeyChar) Or Char.IsControl(e.KeyChar)) Then

            e.Handled = False

        Else

            e.Handled = True

        End If

    End Sub


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub grdWorkData_CellBeginEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles grdWorkData.CellBeginEdit

        If Not String.IsNullOrWhiteSpace(grdWorkData.Item(e.ColumnIndex, e.RowIndex).Value) Then

            If e.ColumnIndex <> 0 And e.ColumnIndex <> 3 And e.ColumnIndex <> 4 And e.ColumnIndex <> 13 Then

                Dim str As String = grdWorkData.Item(e.ColumnIndex, e.RowIndex).Value

                grdWorkData.Item(e.ColumnIndex, e.RowIndex).Value = Functions.ConvertStrHourToInt(str)

            End If

        End If

    End Sub


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub grdWorkData_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles grdWorkData.CellEndEdit

        If Not String.IsNullOrWhiteSpace(grdWorkData.Item(e.ColumnIndex, e.RowIndex).Value) Then

            '' adjust according to user edit
            'flagUserEdit = True

            ' check cells displayed value format time 
            If e.ColumnIndex <> 0 And e.ColumnIndex <> 3 And e.ColumnIndex <> 4 And e.ColumnIndex <> 13 Then

                ' convert from the value is edited to time format 
                grdWorkData.Item(e.ColumnIndex, e.RowIndex).Value = Integer.Parse(grdWorkData.Item(e.ColumnIndex, e.RowIndex).Value).ToString("00:##")

                ' onDutyA and offDutyA
                If e.ColumnIndex = 5 Or e.ColumnIndex = 6 Then

                    AdjustTimeDutyType(e.RowIndex)

                Else

                    'get work type no
                    Dim workTypeNo As String = grdWorkData.Rows.Item(e.RowIndex).Cells.Item(3).Value.ToString()

                    If workTypeNo = 2 Or workTypeNo = 5 Or workTypeNo = 6 Or workTypeNo = 7 Or workTypeNo = 10 Then

                        'clear data 
                        For i As Integer = 5 To 14

                            grdWorkData.Rows.Item(e.RowIndex).Cells.Item(i).Value = Nothing

                        Next

                    End If

                End If

                ' duty type
            ElseIf e.ColumnIndex = 3 Then


                AdjustTimeDutyType(e.RowIndex)

                ' timetable
            ElseIf e.ColumnIndex = 4 Then


                AdjustTimeDutyType(e.RowIndex)


            End If

            ' add row is editing to list
            AddRowEditedToArrList(e.RowIndex)

            grdIsEdited = True

            'calculate details
            CalculateWorkDataDetails()

            'load details
            LoadDetails()

        End If





    End Sub


    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub grdWorkData_CellValidating(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellValidatingEventArgs) Handles grdWorkData.CellValidating

        Dim strErr1 As String = "The time of the following item is incorrect."
        Dim strErr2 As String = "The time of the following item is incorrect." & vbNewLine & "On duty amendment is a time earlier than off duty amendment."
        Dim strErr3 As String = "The time of the following item is incorrect." & vbNewLine & "Off duty amendment is a time later than on duty amendment."
        Dim strErr4 As String = "Duty type is incorrect."

        Dim strValue As String = e.FormattedValue.ToString()

        If Not String.IsNullOrWhiteSpace(strValue) And IsNumeric(strValue) And e.ColumnIndex <> 0 And
            e.ColumnIndex <> 3 And e.ColumnIndex <> 4 And e.ColumnIndex <> 13 Then

            Dim value As Integer = CType(e.FormattedValue, Integer)

            ' Validate hour < 24 and minute < 60
            If (value >= 4800) Or (value Mod 100) > 59 Then

                lblMessage.Text = strErr1

                e.Cancel = True

            End If

            ' validate on duty < off duty when edited cell on duty
            If e.ColumnIndex = 5 And Not String.IsNullOrEmpty(grdWorkData.Item(6, e.RowIndex).Value) Then

                If value >= Functions.ConvertStrHourToInt(grdWorkData.Item(6, e.RowIndex).Value) Then

                    lblMessage.Text = strErr2

                    e.Cancel = True
                End If

            End If

            ' validate off duty > on duty when edited cell off duty
            If e.ColumnIndex = 6 And Not String.IsNullOrEmpty(grdWorkData.Item(5, e.RowIndex).Value) Then

                If value <= Functions.ConvertStrHourToInt(grdWorkData.Item(5, e.RowIndex).Value) Then

                    lblMessage.Text = strErr3

                    e.Cancel = True

                End If

            End If

        ElseIf e.ColumnIndex = 3 Then

            ' only allow holiday duty type for date is holiday and else
            If grdWorkData.CurrentRow.DefaultCellStyle.BackColor = Color.LightPink Then

                If Not dtDutyType.Rows.Item(11).Item("Name").Equals(e.FormattedValue.ToString()) _
                    And Not dtDutyType.Rows.Item(12).Item("Name").Equals(e.FormattedValue.ToString()) Then

                    lblMessage.Text = strErr4

                    e.Cancel = True

                End If

            Else

                If dtDutyType.Rows.Item(11).Item("Name").Equals(e.FormattedValue.ToString()) _
                    Or dtDutyType.Rows.Item(12).Item("Name").Equals(e.FormattedValue.ToString()) Then

                    lblMessage.Text = strErr4

                    e.Cancel = True

                End If

            End If

        End If

    End Sub



    Dim startTime As Integer

    Dim endTime As Integer

    Dim unitMinutes As Integer

    Dim oTUnitminutes As Integer

    Dim timetableNo As String

    Dim timetableExFrom As String

    Dim timetableExTo As String

    Dim delimitation As Integer

    Dim dtRestTimes As Data.DataTable

    Dim onDutyA As Integer

    Dim offDutyA As Integer

    ''' <summary>
    ''' round time and calculate from fp terminal and user edit, get timetable's infor
    ''' </summary>
    ''' <param name="timetable"></param>
    ''' <param name="rowIndex"></param>
    ''' <param name="dtTimetable"></param>
    ''' <remarks></remarks>
    Private Sub FillRow(ByVal timetable As String, ByVal rowIndex As Integer, ByVal dtTimetable As Data.DataTable)


        ' get timetable's infor
        For Each row As DataRow In dtTimetable.Rows

            If timetable.Equals(row.Item("NO")) Then

                startTime = row.Item("WORK_FROM")

                endTime = row.Item("WORK_TO")

                unitMinutes = row.Item("UNIT_MINUTES")

                oTUnitminutes = row.Item("OVER_UNIT_MINUTES")

                timetableNo = row.Item("TIME_TABLE_NO")

                timetableExFrom = row.Item("EXPIRATION_FROM")

                timetableExTo = row.Item("EXPIRATION_TO")

                delimitation = row.Item("DELIMITATION")

                Exit For

            End If

        Next

        ' get rest times
        Dim dtoTimetable As DTOTimetable = New DTOTimetable()

        dtoTimetable.TimetableNoProperty = timetableNo

        dtoTimetable.ExFromProperty = timetableExFrom

        dtoTimetable.ExToProperty = timetableExTo

        dtRestTimes = bsTimetable.GetRestTime(dtoTimetable)


        '------------------------user edit-----------------------------------------------

        ' on duty and off duty is not empty
        If Not String.IsNullOrWhiteSpace(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOnDutyA).Value) _
            And Not String.IsNullOrWhiteSpace(grdWorkData.Rows.Item(rowIndex).Cells(strColOffDutyA).Value) Then

            ' round on duty amendment
            onDutyA = bsWorkData.RoundOnDutyUp(Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOnDutyA).Value),
                                               startTime, unitMinutes, oTUnitminutes)

            grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOnDutyA).Value = onDutyA.ToString("0#:##")


            ' round off duty amendment
            offDutyA = bsWorkData.RoundOffDutyDown(Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOffDutyA).Value),
                                                   endTime, unitMinutes, oTUnitminutes)

            grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOffDutyA).Value = offDutyA.ToString("0#:##")

            ' calculate rest time
            Dim restTime As Integer = bsWorkData.CalculateRestTime(onDutyA, offDutyA, dtRestTimes)

            If restTime > 0 Then

                grdWorkData.Rows.Item(rowIndex).Cells.Item(strColRecess).Value = restTime.ToString("0#:##")

            Else

                grdWorkData.Rows.Item(rowIndex).Cells.Item(strColRecess).Value = Nothing

            End If

            Dim workTime As Integer

            Dim overtime As Integer

            Dim leaveEarly As Integer

            Dim lateness As Integer

            ' calculate Time
            ClearCell(rowIndex)
            bsWorkData.FigureTime(onDutyA, offDutyA, startTime, endTime, restTime, dtRestTimes, workTime, overtime, leaveEarly, lateness)

            grdWorkData.Rows.Item(rowIndex).Cells.Item(strColWork).Value = workTime.ToString("0#:##")

            If overtime > 0 Then

                grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOT).Value = overtime.ToString("0#:##")

            End If

            If leaveEarly > 0 Then

                grdWorkData.Rows.Item(rowIndex).Cells.Item(strColLeaveEarly).Value = leaveEarly.ToString("0#:##")

            End If

            If lateness > 0 Then

                grdWorkData.Rows.Item(rowIndex).Cells.Item(strColLateness).Value = lateness.ToString("0#:##")

            End If

            ' ondutyA is not empty and offdutyA is empty
        ElseIf Not String.IsNullOrWhiteSpace(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOnDutyA).Value) Then

            ' round on duty amendment
            onDutyA = bsWorkData.RoundOnDutyUp(Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOnDutyA).Value),
                                               startTime, unitMinutes, oTUnitminutes)

            grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOnDutyA).Value = onDutyA.ToString("0#:##")

            ' lateness
            If onDutyA > startTime Then

                grdWorkData.Rows.Item(rowIndex).Cells.Item(strColLateness).Value =
                    Business.Functions.SubtractHourReturnHour(onDutyA, startTime).ToString("0#:##")

            End If

            ' offdutyA is not empty and ondutyA is empty
        ElseIf Not String.IsNullOrWhiteSpace(grdWorkData.Rows.Item(rowIndex).Cells(strColOffDutyA).Value) Then

            ' round off duty amendment
            offDutyA = bsWorkData.RoundOffDutyDown(Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOffDutyA).Value),
                                                   endTime, unitMinutes, oTUnitminutes)

            grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOffDutyA).Value = offDutyA.ToString("0#:##")

            ' leave early
            If offDutyA < endTime Then

                grdWorkData.Rows.Item(rowIndex).Cells.Item(strColLeaveEarly).Value =
                    Business.Functions.SubtractHourReturnHour(endTime, offDutyA).ToString("0#:##")

            End If

            '   End If

            ' support to change from duty type is deleted all data to normal type
        Else

            If Not String.IsNullOrWhiteSpace(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOnDuty).Value) _
           And Not String.IsNullOrWhiteSpace(grdWorkData.Rows.Item(rowIndex).Cells(strColOffDuty).Value) Then

                ' round on duty amendment
                onDutyA = bsWorkData.RoundOnDutyUp(Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOnDuty).Value),
                                                   startTime, unitMinutes, oTUnitminutes)

                grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOnDutyA).Value = onDutyA.ToString("0#:##")


                ' round off duty amendment
                offDutyA = bsWorkData.RoundOffDutyDown(Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOffDuty).Value),
                                                       endTime, unitMinutes, oTUnitminutes)

                grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOffDutyA).Value = offDutyA.ToString("0#:##")

                ' calculate rest time
                Dim restTime As Integer = bsWorkData.CalculateRestTime(onDutyA, offDutyA, dtRestTimes)

                If restTime > 0 Then

                    grdWorkData.Rows.Item(rowIndex).Cells.Item(strColRecess).Value = restTime.ToString("0#:##")

                Else

                    grdWorkData.Rows.Item(rowIndex).Cells.Item(strColRecess).Value = Nothing

                End If

                Dim workTime As Integer

                Dim overtime As Integer

                Dim leaveEarly As Integer

                Dim lateness As Integer

                ' calculate Time
                ClearCell(rowIndex)
                bsWorkData.FigureTime(onDutyA, offDutyA, startTime, endTime, restTime, dtRestTimes, workTime, overtime, leaveEarly, lateness)

                grdWorkData.Rows.Item(rowIndex).Cells.Item(strColWork).Value = workTime.ToString("0#:##")

                If overtime > 0 Then

                    grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOT).Value = overtime.ToString("0#:##")

                End If

                If leaveEarly > 0 Then

                    grdWorkData.Rows.Item(rowIndex).Cells.Item(strColLeaveEarly).Value = leaveEarly.ToString("0#:##")

                End If

                If lateness > 0 Then

                    grdWorkData.Rows.Item(rowIndex).Cells.Item(strColLateness).Value = lateness.ToString("0#:##")

                End If

            End If

        End If

    End Sub


    ''' <summary>
    ''' duty type half day leave AM
    ''' </summary>
    ''' <param name="rowIndex"></param>
    ''' <remarks></remarks>
    Private Sub OffAM(ByVal rowIndex As Integer)

        ' fill row and get timetable's infor
        FillRow(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColWorkType).Value, rowIndex, dtTimetable)

        ' calculate start time in the afternoon
        Dim startAffTime As Integer = delimitation

        For Each row As DataRow In dtRestTimes.Rows

            If Integer.Parse(row.Item("REST_FROM")) = delimitation Then

                startAffTime = Integer.Parse(row.Item("REST_TO"))

            End If

        Next

        ' check if start work late according to start time afternoon
        If onDutyA > startAffTime Then

            grdWorkData.Rows.Item(rowIndex).Cells.Item(strColLateness).Value =
                Business.Functions.SubtractHourReturnHour(onDutyA, startAffTime).ToString("0#:##")

        Else

            grdWorkData.Rows.Item(rowIndex).Cells.Item(strColLateness).Value = Nothing

        End If

    End Sub


    ''' <summary>
    ''' duty type half day leave PM
    ''' </summary>
    ''' <param name="rowIndex"></param>
    ''' <remarks></remarks>
    Private Sub OffPM(ByVal rowIndex As Integer)

        ' fill row and get timetable's infor
        FillRow(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColWorkType).Value, rowIndex, dtTimetable)

        'check if leave early according to delimitation
        If offDutyA < delimitation Then

            grdWorkData.Rows.Item(rowIndex).Cells.Item(strColLeaveEarly).Value =
                 Business.Functions.SubtractHourReturnHour(delimitation, offDutyA).ToString("0#:##")

        Else

            grdWorkData.Rows.Item(rowIndex).Cells.Item(strColLeaveEarly).Value = Nothing

        End If


    End Sub


    ''' <summary>
    ''' Adjust all time items according to duty type
    ''' </summary>
    ''' <param name="rowIndex"></param>
    ''' <remarks></remarks>
    Private Sub AdjustTimeDutyType(ByVal rowIndex As Integer)

        'check duty type is not empty
        If Not grdWorkData.Rows.Item(rowIndex).Cells.Item(strColDutyType).Value Is Nothing Then

            If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColDutyType).Value.ToString()) Then

                Dim dutyType As Integer = Integer.Parse(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColDutyType).Value)

                ' duty type = normal and substitutes holiday
                If dutyType = 1 Or dutyType = 9 Then

                    ' holiday have to be empty.
                    grdWorkData.Rows.Item(rowIndex).Cells.Item(strColHoliday).Value = Nothing

                    ' holiday midnight have to be empty
                    ' grdWorkData.Rows.Item(rowIndex).Cells.Item(strColHolidayMidnight).Value = Nothing

                    ' fill row
                    FillRow(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColWorkType).Value, rowIndex, dtTimetable)

                    'day-off
                ElseIf dutyType = 2 Or dutyType = 5 Or dutyType = 6 Or dutyType = 7 Or dutyType = 10 Then

                    'clear all time items value
                    For i As Integer = 5 To 12

                        grdWorkData.Rows.Item(rowIndex).Cells.Item(i).Value = Nothing

                    Next

                    ' half day off AM
                ElseIf dutyType = 3 Then

                    OffAM(rowIndex)

                    ' half day off PM
                ElseIf dutyType = 4 Then

                    OffPM(rowIndex)


                ElseIf dutyType = 11 Or dutyType = 12 Or dutyType = 13 Then

                    ' get delimitation
                    For Each row As DataRow In dtTimetable.Rows

                        If grdWorkData.Rows.Item(rowIndex).Cells.Item(strColWorkType).Equals(row.Item("NO")) Then

                            delimitation = row.Item("DELIMITATION")

                            Exit For

                        End If

                    Next

                    ' check off AM or Off PM
                    If Business.Functions.SubtractHourReturnHour(delimitation,
                      Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOnDutyA).Value)) >
                      Business.Functions.SubtractHourReturnHour(Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(
                                                                    rowIndex).Cells.Item(strColOffDutyA).Value), delimitation) Then

                        OffPM(rowIndex)

                    Else

                        OffAM(rowIndex)

                    End If

                    ' holiday duty
                ElseIf dutyType = 8 Then

                    ' fill row
                    FillRow(grdWorkData.Rows.Item(rowIndex).Cells.Item(strColWorkType).Value, rowIndex, dtTimetable)

                    ' set working time into holiday and clear working time in working
                    grdWorkData.Rows.Item(rowIndex).Cells.Item(strColHoliday).Value = grdWorkData.Rows.Item(rowIndex).Cells.Item(strColWork).Value

                    grdWorkData.Rows.Item(rowIndex).Cells.Item(strColWork).Value = Nothing

                End If

            End If

        End If

        ''calculate details
        'CalculateWorkDataDetails()

        ''load details
        'LoadDetails()


    End Sub

    ''' <summary>
    ''' Clear ot, work , leave early, lateness
    ''' </summary>
    ''' <param name="rowIndex"></param>
    ''' <remarks></remarks>
    Private Sub ClearCell(ByVal rowIndex As Integer)
        ' set work time
        grdWorkData.Rows.Item(rowIndex).Cells.Item(strColWork).Value = Nothing

        ' set leave early
        grdWorkData.Rows.Item(rowIndex).Cells.Item(strColLeaveEarly).Value = Nothing

        ' set lateness
        grdWorkData.Rows.Item(rowIndex).Cells.Item(strColLateness).Value = Nothing

        ' set overtime
        grdWorkData.Rows.Item(rowIndex).Cells.Item(strColOT).Value = Nothing
    End Sub

    ''' <summary>
    ''' turn back to previous month
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click

        If grdIsEdited Then

            If MessageBox.Show(msg, "Message", MessageBoxButtons.OKCancel) = DialogResult.OK Then

                LoadPreviousMonth()


            End If

        Else

            LoadPreviousMonth()

        End If


    End Sub


    ''' <summary>
    ''' Load previous month when click btn previous
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub LoadPreviousMonth()


        Me.month = Me.month - 1

        If Me.month < 1 Then

            Me.month = 12

            Me.year = Me.year - 1

        End If

        ' get start and end date
        bsWorkData.GetStartEndDateOfMonth(organRootNo, month, year, startDate, endDate)

        ' kiem tra nhan vien co ton tai trong thang ko
        If exFrom <= Integer.Parse(endDate.ToString("yyyyMMdd")) And
            exTo >= Integer.Parse(startDate.ToString("yyyyMMdd")) Then

            lblMessage.Text = "Working....."

            System.Windows.Forms.Application.DoEvents()

            ' clear datagridview
            grdWorkData.Rows.Clear()

            txtPeriodOfTime.Text = Me.startDate.ToString("dd/MMMM/yyyy") & " - " & Me.endDate.ToString("dd/MMMM/yyyy")

            'clear details
            ResetDetails()

            contract = 0

            ' load datagridview
            LoadWorkData()

            System.Windows.Forms.Application.DoEvents()

            lblMessage.Text = Nothing

        Else

            lblMessage.Text = " No Data."

            Me.month = Me.month + 1

            If Me.month > 12 Then

                Me.month = 1

                Me.year = Me.year + 1

            End If

        End If
        
    End Sub


    Dim msg As String = "Are you sure to cancel the update data?"


    ''' <summary>
    ''' turn next to following month
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click

        If grdIsEdited Then

            If MessageBox.Show(msg, "Message", MessageBoxButtons.OKCancel) = DialogResult.OK Then

                LoadNextMonth()


            End If

        Else

            LoadNextMonth()

        End If


    End Sub

    ''' <summary>
    ''' Load next month when click btn Next
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub LoadNextMonth()
        
        Me.month = Me.month + 1

        If Me.month > 12 Then

            Me.month = 1

            Me.year = Me.year + 1

        End If

        ' get start and end date
        bsWorkData.GetStartEndDateOfMonth(organRootNo, month, year, startDate, endDate)

        ' kiem tra nhan vien co ton tai trong thang ko
        If exFrom <= Integer.Parse(endDate.ToString("yyyyMMdd")) And
            exTo >= Integer.Parse(startDate.ToString("yyyyMMdd")) Then

            lblMessage.Text = "Working....."

            System.Windows.Forms.Application.DoEvents()

            txtPeriodOfTime.Text = Me.startDate.ToString("dd/MMMM/yyyy") & " - " & Me.endDate.ToString("dd/MMMM/yyyy")

            ' clear datagridview
            grdWorkData.Rows.Clear()

            'clear details
            ResetDetails()

            contract = 0

            ' load datagridview
            LoadWorkData()

            System.Windows.Forms.Application.DoEvents()

            lblMessage.Text = Nothing

        Else

            lblMessage.Text = "No Data."

            Me.month = Me.month - 1

            If Me.month < 1 Then

                Me.month = 12

                Me.year = Me.year - 1

            End If

        End If

    End Sub



    ''' <summary>
    ''' update
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        If arrRowEdited.Count <> 0 Then

            lblMessage.Text = "Working..."

            System.Windows.Forms.Application.DoEvents()



            System.Windows.Forms.Application.DoEvents()

            If UpdateWorkDate(arrRowEdited) Then

                lblMessage.Text = "Update is successful."

                arrRowEdited.Clear()

                grdIsEdited = False

            Else

                lblMessage.Text = "Update is not successful."

                grdIsEdited = True

            End If

        End If

    End Sub



    ''' <summary>
    ''' update time
    ''' </summary>
    ''' <param name="arrRowEdited"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateWorkDate(ByVal arrRowEdited As ArrayList) As Boolean

        ' get row has edited in list and update to database
        For i As Integer = 0 To arrRowEdited.Count - 1

            Dim strDate As String = grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColDate).Value.ToString()

            strDate = strDate.Substring(0, 10)

            Dim strDates As String() = strDate.Split("/")

            strDate = strDates(2) & strDates(1) & strDates(0)

            ' MsgBox(dgvWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColDutyType).Value)

            Dim workDays As Double = 0

            Dim paidVacation As Double = 0

            Dim specialHolidays As Double = 0

            Dim absenceDays As Double = 0

            Dim holidayDays As Double = 0

            Dim compensatoryDayOff As Double = 0

            Dim workContract As Integer

            ' calculate work contract
            bsWorkData.GetTimetableEmployee(empNo, exFrom, exTo, workContract)

            ' work day type
            Dim workDayType As Integer = grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColDutyType).Value

            'normal duty
            If workDayType = 1 Then

                workDays = 1

                ' paid leave
            ElseIf workDayType = 2 Then

                paidVacation = 1

                workContract = 0

                'half Day leave
            ElseIf workDayType = 3 Or workDayType = 4 Then

                workDays = 0.5

                paidVacation = 0.5

                ' special leave
            ElseIf workDayType = 5 Then

                specialHolidays = 1

                workContract = 0

                ' replacement leave
            ElseIf workDayType = 6 Then

                compensatoryDayOff = 1

                workContract = 0

                'Absence
            ElseIf workDayType = 7 Then

                absenceDays = 1

                workContract = 0

                'holiday
            ElseIf workDayType = 8 Then

                holidayDays = 1

                workContract = 0

                ' subsitute holiday
            ElseIf workDayType = 9 Then

                workDays = 1

                ' sick leave 
            ElseIf workDayType = 10 Then

                ' half day special leave
            ElseIf workDayType = 11 Then

                workDays = 0.5

                specialHolidays = 0.5

                'half day sick leave
            ElseIf workDayType = 12 Then

                workDays = 0.5

                'Unpaid Half Day Leave
            ElseIf workDayType = 13 Then

                workDays = 0.5

                absenceDays = 0.5

            End If

            Dim timetableNo As String

            Dim timetableExFrom As String

            Dim timetableExTo As String

            GetTimetable(grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColWorkType).Value,
                         timetableNo, timetableExFrom, timetableExTo)

            ' update xun DB
            If Not bsWorkData.Update(Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColOnDuty).Value),
                                 Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColOffDuty).Value),
                                 Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColOnDutyA).Value),
                                 Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColOffDutyA).Value),
                                 timetableNo, timetableExFrom, timetableExTo,
                                 Functions.ConvertHourToMinutes(Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColRecess).Value)),
                                 organNo, empNo, strDate,
                                 Functions.ConvertHourToMinutes(Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColWork).Value)),
                                 Functions.ConvertHourToMinutes(Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColOT).Value)),
                                 Functions.ConvertHourToMinutes(Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColLateness).Value)),
                                 Functions.ConvertHourToMinutes(Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColLeaveEarly).Value)),
                                 grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColMemo).Value, workDays, paidVacation, specialHolidays, absenceDays,
                                 holidayDays, compensatoryDayOff, workDayType, workContract,
                                 Functions.ConvertHourToMinutes(Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(arrRowEdited.Item(i)).Cells.Item(strColHoliday).Value))) Then

                ' lblMessage.Text = "The update is successful."

                Return False

            End If
            ' End If
        Next

        Return True

    End Function





    ''' <summary>
    ''' Check and add row edited to list of rows edited (arrRowEdited)
    ''' </summary>
    ''' <param name="rowIndex"></param>
    ''' <remarks></remarks>
    Private Sub AddRowEditedToArrList(ByVal rowIndex As Integer)

        ' check if row is existed in list
        Dim added As Boolean = False

        For i As Integer = 0 To arrRowEdited.Count - 1

            If rowIndex = arrRowEdited.Item(i) Then

                added = True

                Exit For

            End If

        Next

        ' if row is not in list, add
        If Not added Then

            arrRowEdited.Add(rowIndex)

        End If

    End Sub





    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub grdWorkData_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles grdWorkData.KeyDown

        If e.KeyCode = Keys.Delete Then

            'clear data 
            For i As Integer = 5 To grdWorkData.Columns.Count - 1

                grdWorkData.CurrentRow.Cells.Item(i).Value = Nothing

            Next

            ' add row is editing to list
            AddRowEditedToArrList(grdWorkData.CurrentCell.RowIndex)

            grdIsEdited = True

            ' calculate and load details
            CalculateWorkDataDetails()

            LoadDetails()

        End If

    End Sub


    ''' <summary>
    ''' Get timetable No, exfrom, exTo according to NO
    ''' </summary>
    ''' <param name="no"></param>
    ''' <param name="timetableNo"></param>
    ''' <param name="exFrom"></param>
    ''' <param name="exTo"></param>
    ''' <remarks></remarks>
    Private Sub GetTimetable(ByVal no As String, ByRef timetableNo As String, ByRef exFrom As String, ByRef exTo As String)

        For i As Integer = 0 To dtTimetable.Rows.Count - 1

            If dtTimetable.Rows.Item(i).Item("NO").ToString().Equals(no) Then

                timetableNo = dtTimetable.Rows.Item(i).Item("TIME_TABLE_NO")

                exFrom = dtTimetable.Rows.Item(i).Item("EXPIRATION_FROM")

                exTo = dtTimetable.Rows.Item(i).Item("EXPIRATION_TO")

                Exit For

            End If

        Next


    End Sub

    Private Sub ResetDetails()

        contract = 0

        working = 0

        overtime = 0

        holiday = 0

        paidLeave = 0

        replace = 0

        absence = 0

        special = 0

        lateness = 0

        leaveEarly = 0

    End Sub

    ''' <summary>
    ''' output work data of an employee
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub btnOutput_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOutput.Click

        Dim saveDialog As SaveFileDialog = New SaveFileDialog()

        saveDialog.Filter = "MS Excel files (*.xls)|*.xls"

        saveDialog.FileName = "WorkData.xls"

        ' show save dialog
        If saveDialog.ShowDialog() = DialogResult.OK Then

            lblMessage.Text = "Working..."

            System.Windows.Forms.Application.DoEvents()

            xlApp = New Excel.Application

            xlBook = xlApp.Workbooks.Open(System.Windows.Forms.Application.StartupPath & "\OutputTemplate.xls")

            xlSheet = xlBook.ActiveSheet

            ' set employee No
            SetRangeValue(xlSheet, "C", 4, empNo)

            ' set employee Name
            SetRangeValue(xlSheet, "F", 4, empName)

            ' set month/year
            SetRangeValue(xlSheet, "M", 4, month.ToString() & "/" & year.ToString())

            ' export datagridview to excel
            For i As Integer = 0 To grdWorkData.Rows.Count - 1

                ' column date
                xlSheet.Cells(7 + i, 1) = grdWorkData.Rows.Item(i).Cells.Item(strColDate).Value

                ' column duty type
                xlSheet.Cells(7 + i, 2) = grdWorkData.Rows.Item(i).Cells.Item(strColDutyType).FormattedValue

                'column timetable
                xlSheet.Cells(7 + i, 3) = grdWorkData.Rows.Item(i).Cells.Item(strColWorkType).FormattedValue

                'column onduty
                xlSheet.Cells(7 + i, 4) = grdWorkData.Rows.Item(i).Cells.Item(strColOnDuty).Value

                'colum off duty
                xlSheet.Cells(7 + i, 5) = grdWorkData.Rows.Item(i).Cells.Item(strColOffDuty).Value

                'column onduty A
                xlSheet.Cells(7 + i, 6) = grdWorkData.Rows.Item(i).Cells.Item(strColOnDutyA).Value

                'column off duty A
                xlSheet.Cells(7 + i, 7) = grdWorkData.Rows.Item(i).Cells.Item(strColOffDutyA).Value

                'column work
                xlSheet.Cells(7 + i, 8) = grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value

                'column overtime
                xlSheet.Cells(7 + i, 9) = grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value

                'column recess
                xlSheet.Cells(7 + i, 10) = grdWorkData.Rows.Item(i).Cells.Item(strColRecess).Value

                'column lateness
                xlSheet.Cells(7 + i, 11) = grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value

                'column leave early
                xlSheet.Cells(7 + i, 12) = grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value

                'column holiday
                xlSheet.Cells(7 + i, 13) = grdWorkData.Rows.Item(i).Cells.Item(strColHoliday).Value


                'column memo
                xlSheet.Cells(7 + i, 14) = grdWorkData.Rows.Item(i).Cells.Item(strColMemo).Value

            Next

            Dim count As Integer = grdWorkData.Rows.Count

            'total
            xlSheet.Cells(7 + grdWorkData.Rows.Count, 1) = "Total"

            'total working hour
            xlSheet.Cells(7 + grdWorkData.Rows.Count, 8) = txtWorking.Text

            'total overtime
            xlSheet.Cells(7 + grdWorkData.Rows.Count, 9) = txtOvertime.Text

            'total holiday
            xlSheet.Cells(7 + grdWorkData.Rows.Count, 13) = txtHoliday.Text

            count += 2

            ' paid leave day
            xlSheet.Cells(7 + count, 1) = "Paid Leave Days:"

            xlSheet.Cells(7 + count, 2) = txtPaidLeave.Text





            'replace day
            xlSheet.Cells(7 + count, 3) = "Replace Days:"

            xlSheet.Cells(7 + count, 4) = txtReplace.Text

            'absence days
            xlSheet.Cells(7 + count, 5) = "Absence Days:"

            xlSheet.Cells(7 + count, 6) = txtAbsence.Text

            'special leave days
            xlSheet.Cells(7 + count, 7) = "Special Leave Days:"

            xlSheet.Cells(7 + count, 8) = txtSpecial.Text


            'leave early days
            xlSheet.Cells(7 + count, 9) = "Leave Early Days:"

            xlSheet.Cells(7 + count, 10) = txtEarly.Text

            ' lateness days
            xlSheet.Cells(7 + count, 11) = "Lateness Days:"

            xlSheet.Cells(7 + count, 12) = txtLateness.Text



            For Each cells As Range In xlSheet.Range("A" & (grdWorkData.Rows.Count + 7).ToString() & ": N" & (grdWorkData.Rows.Count + 9))

                cells.Font.Bold = True

                cells.EntireColumn.AutoFit()

                cells.HorizontalAlignment = XlHAlign.xlHAlignLeft

            Next


            'set border
            For Each cells As Range In xlSheet.Range("A7" & ": N" & (grdWorkData.Rows.Count + 7))

                cells.BorderAround()

            Next

            xlBook.SaveAs(saveDialog.FileName)

            xlBook.Close()

            xlApp.Quit()

            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet)

            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook)

            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

            System.Windows.Forms.Application.DoEvents()

            lblMessage.Text = Nothing

            Process.Start(saveDialog.FileName)


        End If


    End Sub


    ''' <summary>
    ''' set range value
    ''' </summary>
    ''' <param name="tSheet"></param>
    ''' <param name="columnLabel"></param>
    ''' <param name="rowNum"></param>
    ''' <param name="tValue"></param>
    ''' <remarks></remarks>
    Private Sub SetRangeValue(ByVal tSheet As Object, ByVal columnLabel As String, ByVal rowNum As Integer, ByVal tValue As Object)

        Dim rangName As String = columnLabel & rowNum

        tSheet.Range(rangName).Value = tValue

    End Sub

    Private Sub CalculateWorkDataDetails()

        ResetDetails()

        Dim contractminutesInDay As Integer

        For i As Integer = 0 To grdWorkData.Rows.Count - 1

            ' sum of contract hour
            'get timetable no, exfrom, exto
            Dim timetableNo As String

            Dim timetableExFrom As String

            Dim timetableExTo As String

            GetTimetable(grdWorkData.Rows.Item(i).Cells.Item(strColWorkType).Value,
                         timetableNo, timetableExFrom, timetableExTo)

            contractminutesInDay = bsWorkData.GetContractMinutesInDay(timetableNo, timetableExFrom, timetableExTo)

            If grdWorkData.Rows.Item(i).DefaultCellStyle.BackColor = Color.LightPink Then

                contractminutesInDay = 0

            End If

            ' caculate contract hour
            contract += contractminutesInDay


            If Not grdWorkData.Rows.Item(i).Cells.Item(strColDutyType).Value Is Nothing Then

                ' work day type
                Dim workDayType As Integer = grdWorkData.Rows.Item(i).Cells.Item(strColDutyType).Value

                'normal duty
                If workDayType = 1 Then

                    ' working
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value.ToString()) Then

                            working += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value))

                        End If

                    End If

                    'ovetime
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value.ToString()) Then

                            overtime += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value))

                        End If

                    End If

                    ' leave early
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value.ToString()) Then

                            leaveEarly += 1

                        End If

                    End If

                    ' lateness
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value.ToString()) Then

                            lateness += 1

                        End If

                    End If


                    ' paid leave
                ElseIf workDayType = 2 Then

                    paidLeave += 1

                    'half Day leave
                ElseIf workDayType = 3 Or workDayType = 4 Then

                    ' working
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value.ToString()) Then

                            working += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value))

                        End If


                    End If

                    'ovetime
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value.ToString()) Then

                            overtime += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value))

                        End If

                    End If

                    ' leave early
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value.ToString()) Then

                            leaveEarly += 1

                        End If

                    End If

                    ' lateness
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value.ToString()) Then

                            lateness += 1

                        End If

                    End If

                    paidLeave += 0.5

                    ' special leave
                ElseIf workDayType = 5 Then

                    special += 1

                    ' replacement leave
                ElseIf workDayType = 6 Then

                    replace += 1

                    'Absence
                ElseIf workDayType = 7 Then

                    absence += 1

                    'holiday
                ElseIf workDayType = 8 Then

                    'working
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColHoliday).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColHoliday).Value.ToString()) Then

                            holiday += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColHoliday).Value))

                        End If


                    End If

                    'ovetime
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value.ToString()) Then

                            overtime += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value))

                        End If

                    End If

                    ' leave early
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value.ToString()) Then

                            leaveEarly += 1

                        End If

                    End If

                    ' lateness
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value.ToString()) Then

                            lateness += 1

                        End If

                    End If

                    ' subsitute holiday
                ElseIf workDayType = 9 Then

                    'working
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value.ToString()) Then

                            working += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value))

                        End If


                    End If

                    'ovetime
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value.ToString()) Then

                            overtime += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value))

                        End If

                    End If

                    ' leave early
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value.ToString()) Then

                            leaveEarly += 1

                        End If

                    End If

                    ' lateness
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value.ToString()) Then

                            lateness += 1

                        End If

                    End If

                    ' sick leave 
                ElseIf workDayType = 10 Then

                    ' half day special leave
                ElseIf workDayType = 11 Then

                    'woring
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value.ToString()) Then

                            working += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value))

                        End If


                    End If

                    'ovetime
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value.ToString()) Then

                            overtime += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value))

                        End If

                    End If

                    ' leave early
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value.ToString()) Then

                            leaveEarly += 1

                        End If

                    End If

                    ' lateness
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value.ToString()) Then

                            lateness += 1

                        End If

                    End If

                    special += 0.5

                    'half day sick leave
                ElseIf workDayType = 12 Then

                    'working
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value.ToString()) Then

                            working += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value))

                        End If


                    End If

                    'ovetime
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value.ToString()) Then

                            overtime += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value))

                        End If

                    End If

                    ' leave early
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value.ToString()) Then

                            leaveEarly += 1

                        End If

                    End If

                    ' lateness
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value.ToString()) Then

                            lateness += 1

                        End If

                    End If

                    'Unpaid Half Day Leave
                ElseIf workDayType = 13 Then

                    ' overtime
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value.ToString()) Then

                            working += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColWork).Value))

                        End If


                    End If

                    'ovetime
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value.ToString()) Then

                            overtime += Business.Functions.ConvertHourToMinutes(
                                Business.Functions.ConvertStrHourToInt(grdWorkData.Rows.Item(i).Cells.Item(strColOT).Value))

                        End If

                    End If

                    ' leave early
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLeaveEarly).Value.ToString()) Then

                            leaveEarly += 1

                        End If

                    End If

                    ' lateness
                    If Not grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value Is Nothing Then

                        If Not String.IsNullOrEmpty(grdWorkData.Rows.Item(i).Cells.Item(strColLateness).Value.ToString()) Then

                            lateness += 1

                        End If

                    End If


                    absence += 0.5

                End If

            End If

        Next


    End Sub


End Class